package com.ruoyi.test;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelDemo {
    public static void main(String[] args) throws Exception {
        String path  ="C:\\Users\\18203\\Downloads\\企业基地_模板_1686375933128.xlsx";
        try {
            // 创建文件输入流
            FileInputStream inputStream = new FileInputStream(path);
            // 创建工作簿对象
            Workbook workbook = new XSSFWorkbook(inputStream);
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            // 遍历行
            for (Row row : sheet) {
                System.out.println(111);
                // 遍历单元格
                for (Cell cell : row) {
                    // 获取单元格的值
                    CellType type = cell.getCellType();
                    if (type == CellType.STRING) {
                        String value = cell.getStringCellValue();
                        System.out.print(value + "\t");
                    } else if (type == CellType.NUMERIC) {
                        double value = cell.getNumericCellValue();
                        System.out.print(value + "\t");
                    } else if (type == CellType.BOOLEAN) {
                        boolean value = cell.getBooleanCellValue();
                        System.out.print(value + "\t");
                    } else if (type == CellType.BLANK) {
                        System.out.print("\t");
                    } else {
                        System.out.print("\t");
                    }
                }
                System.out.println();
            }
            // 关闭文件输入流和工作簿对象
            inputStream.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static void writeToExcel(String filePath) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Sheet1");

        // 添加数据到表格
        String[] countries = new String[] {"China", "United States", "Japan"};
        String[] countryCodes = new String[] {"CN", "US", "JP"};
        for (int i = 0; i < countries.length; i++) {
            XSSFRow row = sheet.createRow(i);
            XSSFCell countryCell = row.createCell(0);
            XSSFCell codeCell = row.createCell(1);
            countryCell.setCellValue(countries[i]);
            codeCell.setCellValue(countryCodes[i]);
        }

        // 设置数据验证规则
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        int columnIndex = 0; // 列索引，这里设置为第一列
        int startRowIndex = 0; // 起始行索引
        int endRowIndex = countries.length - 1; // 结束行索引
        CellRangeAddressList addressList = new CellRangeAddressList(startRowIndex, endRowIndex, columnIndex, columnIndex);

        // 将数据验证规则应用于单元格
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(getCountryFormula(startRowIndex, endRowIndex));
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);

        // 写入到文件
        FileOutputStream outputStream = new FileOutputStream(filePath);
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    private static String getCountryFormula(int startRowIndex, int endRowIndex) {
        String valueRange = "Sheet1!$B$1:$B$" + endRowIndex;
        String labelRange = "Sheet1!$A$1:$A$" + endRowIndex;
        String formula = "VLOOKUP(" + valueRange + ",{" + valueRange + "," + labelRange + "},2,0)";
        return formula;
    }
}
